package com.dhgate.spu.analysis.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/** 
 * @author:      wangguodong 
 * @create:      2014-2-13 上午11:51:36 
 * @description: 
 */
public class JDBCUtil {
	protected static final Logger logger;
	static {
		logger = LoggerFactory.getLogger(JDBCUtil.class);
	}

	private Connection conn = null;

	private Statement stmt = null;

	private ConnectionPool pool = null;

	private PreparedStatement prepstmt = null;

	private PreparedStatement prepstmtsub = null;

	public JDBCUtil instance = null;
	

	public static ConnectionPool createConnpool(String dbdriver,String url,String username,String dbpasswd,int initialConnections,int incrementalConnections,int maxConnections,int waittime){
		ConnectionPool pool=null;
		pool = new ConnectionPool(dbdriver,url,username,dbpasswd, initialConnections, incrementalConnections, maxConnections, waittime);
		try {
			pool.createPool();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			logger.error("create Pool error: " + e);
		}
		return pool;
	}
	
	public static ConnectionPool createConnpool(ConnectionPool pool){
		try {
			pool.createPool();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			logger.error("create Pool error: " + e);
		}
		return pool;
	}
	/**
	 * 私有构造函数
	 * 
	 * @throws Exception
	 * 
	 * @throws Exception
	 * 
	 */
	public JDBCUtil(ConnectionPool pool) throws Exception {
		this.pool=pool;
		conn = pool.getConnection();

	}


	/**
	 * 执行sql query
	 * 
	 * @param sqlStr
	 * @return
	 */
	public ResultSet executeQuery(String sqlStr) {
		try {
			prepstmt = conn.prepareStatement(sqlStr);
			return prepstmt.executeQuery();
		} catch (Exception e) {
			logger.error("JDBCUtil:executeQuery(" + sqlStr + ")", e);
		}
		return null;
	}
	/**
	 * 执行sql query
	 * 
	 * @param sqlStr
	 * @return
	 */
	public ResultSet executeQuery(String sqlStr, String[] paras) {
		try {
			prepstmt = conn.prepareStatement(sqlStr);
			if (paras != null) {
				for (int i = 0; i < paras.length; i++) {
					prepstmt.setString(i+1, paras[i]);
				}
			}
			return prepstmt.executeQuery();
		} catch (Exception e) {
			logger.error("JDBCUtil:executeQuery(" + sqlStr + ")"
					+ e.getMessage());
		}
		return null;
	}

	/**
	 * 执行sql query
	 * 
	 * @param sqlStr
	 * @return
	 */
	public ResultSet executeQueryLongs(String sqlStr, Long[] paras) {

		try {
			prepstmt = conn.prepareStatement(sqlStr);
			if (paras != null) {
				for (int i = 0; i < paras.length; i++) {
					prepstmt.setLong(i+1, paras[i]);
				}
			}
			return prepstmt.executeQuery();
		} catch (Exception e) {
			logger.error("JDBCUtil:executeQuery(" + sqlStr + ")"
					+ e.getMessage());
		}
		return null;
	}

	/**
	 * 执行sql query
	 * 
	 * @param sqlStr
	 * @return
	 */
	public ResultSet executeQuery(String sqlStr, String[] paras,int start,int end) {
		try {
			prepstmt = conn.prepareStatement(sqlStr);
			int index=0;
			if (paras != null) {
				for (int i = 0; i < paras.length; i++) {
					prepstmt.setString(i+1, paras[i]);
				}
				index=index+paras.length;
			}
			
			prepstmt.setInt(++index, start);
			prepstmt.setInt(++index, end);
			return prepstmt.executeQuery();
		} catch (Exception e) {
			logger.error("JDBCUtil:executeQuery(" + sqlStr + ")"
					+ e.getMessage());
		}
		return null;
	}
	/**
	 * 执行sql query
	 * 
	 * @param sqlStr
	 * @return
	 */
	public ResultSet executeQuerySub(String sqlStr) {
		try {
			prepstmtsub = conn.prepareStatement(sqlStr);
			return prepstmtsub.executeQuery();
		} catch (Exception e) {
			logger.error("JDBCUtil:executeQuery(" + sqlStr + ")"
					+ e.getMessage());
		}
		return null;
	}

	public int executeUpdate(String sqlStr) throws SQLException {
		int count = -1;
		try {
			PreparedStatement prepstmt = conn.prepareStatement(sqlStr);
			count = prepstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.warn("JDBCUtil:executeUpdate(" + sqlStr + ")"
					+ e.getMessage());

			throw e;
		}finally{
			if(prepstmt!=null){
				prepstmt.close();
			}
		}
		return count;
	}

	public int executeUpdate(String sqlStr, int[] intParas) throws SQLException {
		int count = -1;
		try {
			prepstmt = conn.prepareStatement(sqlStr);
			for (int i = 0; i < intParas.length; i++) {
				prepstmt.setInt(i+1, intParas[i]);
			}
			count = prepstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.warn("JDBCUtil:executeUpdate(" + sqlStr + ")"
					+ e.getMessage());

			throw e;
		}
		return count;
	}

	public int executeUpdate(String sqlStr,String[] paras) throws SQLException {
		int count = -1;
		try {
			prepstmt = conn.prepareStatement(sqlStr);

			if (paras != null) {
				for (int i = 0; i < paras.length; i++) {
					prepstmt.setString(i+1, paras[i]);
				}
			}
			count = prepstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.warn("JDBCUtil:executeUpdate(" + sqlStr + ")"
					+ e.getMessage());

			throw e;
		}
		return count;
	}
    public int executeUpdate(String sqlStr,List<String[]> list) throws SQLException {
        int count = -1;
        try {
            conn.setAutoCommit(false);
            prepstmt = conn.prepareStatement(sqlStr);
            for (String[] paras : list) {
                
                if (paras != null) {
                    for (int i = 0; i < paras.length; i++) {
                        prepstmt.setString(i+1, paras[i]);
                    }
                }
                count = prepstmt.executeUpdate();
            }
            conn.commit();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            logger.warn("JDBCUtil:executeUpdate(" + sqlStr + ")"
                    + e.getMessage());

            throw e;
        }
        return count;
    }
	/**
	 * 关闭数据库的链接
	 * 
	 */
	public void closedb() {
		try {
			if (stmt != null) {
				stmt.close();
			}
			if (prepstmt != null) {
				prepstmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (Exception e) {
			logger.error("JDBCUtil:closedb()" + e.getMessage());
		}
	}

	public void closePrepStmt() {
		try {
			if (prepstmt != null) {
				prepstmt.close();
			}
			prepstmt = null;
			if (stmt != null) {
				stmt.close();
			}
			stmt = null;
			pool.returnConnection(conn);
		} catch (Exception e) {
			logger.error("JDBCUtil:closePrepStmt()" + e.getMessage());
		}
	}

	public void closePrepStmtSub() {
		try {
			if (prepstmtsub != null) {
				prepstmtsub.close();
			}
			prepstmtsub = null;
		} catch (Exception e) {
			logger.error("JDBCUtil:closePrepStmt()" + e.getMessage());
		}
	}
}
